package cn.lanqiao.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import cn.lanqiao.entity.Score;

@Mapper
public interface ScoreMapper {
    /**
     * @param score 添加一条成绩记录
     * @return
     */
    @Options(useGeneratedKeys = true,keyProperty = "scoreId")
    @Insert("insert into score(examCode,studentId,subject,ptScore,etScore,score,answerDate,pass) values(#{examCode},#{studentId},#{subject},#{ptScore},#{etScore},#{score},#{answerDate},#{pass})")
    public int add(Score score);

    @Select("select scoreId,examCode,studentId,subject,ptScore,etScore,score,answerDate from score order by scoreId desc")
    public List<Score> findAll();

    // 分页
    @Select("select * from score where studentId = #{studentId} order by scoreId desc")
    public IPage<Score> findById(Page<?> page, Integer studentId);

    // 不分页
    @Select("select * from score where studentId = #{studentId}")
    public List<Score> findById(Integer studentId);

    @Select("select max(etScore) as etScore from score where examCode = #{examCode} group by studentId")
    public List<Score> findByExamCode(Integer examCode);
    
    @Select("select subject,score from score where subject = #{subject}")
    public List<Score> findBySubject(String subject);
    
    @Select("select subject, avg(score) as score from score group by subject")
    public List<Score> findByAvg();
    
    @Select("select subject, max(score) as score from score where subject = #{subject} ")
    public List<Score> findByMax(String subject);
    
    @Select("select subject, min(score) as score from score where subject = #{subject} ")
    public List<Score> findByMin(String subject);
    
    @Select("select (select count(*) from score where score >=60) /(select COUNT(*) from score) as pass from score where subject= #{subject}")
    public List<Score> findByPass(String subject);
    
    @Select("select subject, min(score) as ptScore, max(score) as etScore from score group by subject")
    public List<Score> findByValue();
    
    
}
